﻿Imports System.Data
Imports System.Data.SqlClient
Imports Conexao_liion

Partial Class SplitData
    Inherits System.Web.UI.Page


    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim valida As String = Session("logado")

        'lblMsg.Visible = False

        'Faz com que ao sair do aplicativo, limpe o buffer e não podendo entrar na página através do butão voltar do Browser.
        Response.Cache.SetCacheability(HttpCacheability.ServerAndNoCache)
        'Fim

        Response.Cache.SetExpires(DateTime.Now.AddSeconds(1))

        lblModuloItem.Text = "Split"

        lblMsg.Text = ""

        If Not Page.IsPostBack Then

 
            LoadHolding(True)

            FillDDL()

            Try

                If (Session("strOperacao") = "C" Or Session("strOperacao") = "A" Or Session("strOperacao") = "E") Then ' Consultar | Alterar | Excluir
                    LoadData()
                End If

                If (Session("strOperacao") = "C") Then ' Consultar
                    btnGravar.Enabled = False
                End If

            Catch ex As Exception
                lblMsg.Text = ex.Message
            End Try

        End If


    End Sub

    Private Function LoadHolding(ByVal strConn As String) As Boolean

        LoadHolding = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String = "SELECT CodigoDaHolding, NomeDaHolding" & _
                         " FROM Holding;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                If (theSqlDataReader.HasRows = True) Then

                    ' Loop through all the rows in the DataTableReader.
                    Do While theSqlDataReader.Read()
                        lblHolding.Text = theSqlDataReader(1).ToString
                        lblHolding.ToolTip = theSqlDataReader(0).ToString
                    Loop

                    LoadHolding = True

                Else
                    lblMsg.Text = "Holding não cadastrada no Sistema."
                End If

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Function

    Private Sub FillDDL()

        Dim theQueryString As String = ""

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Try

                Dim mydatatable As New DataTable
                Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()

                theQueryString = "SELECT CodigoDaCia, NomeDaCia" & _
                                 " FROM Companhia" & _
                                 " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
                                 " ORDER BY CodigoDaCia;"

                theSqlCommand.CommandText = theQueryString
                theSqlConnection.Open()

                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)

                ddlCompanhia.DataTextField = "NomeDaCia"
                ddlCompanhia.DataValueField = "CodigoDaCia"
                ddlCompanhia.DataSource = mydatatable
                ddlCompanhia.DataBind()

                ddlCompanhia.Items.Insert(0, "")

                theSqlDataReader.Close()

            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Sub


    Private Sub LoadData()

        Dim theQueryString As String = ""

        ddlCompanhia.Enabled = False

        If (Session("strOperacao").ToString = "C" Or Session("strOperacao").ToString = "E") Then ' Consultar | Excluir

            txtDescSplit.Enabled = False

        End If

        ddlCompanhia.SelectedValue = Session("strCodigoDaCia").ToString
        lblCodSplit.Text = Session("strCodSplit")

        'txtDescSplit.Text = Session("strDDE").ToString

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Try
                Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()

                theQueryString = " SELECT" & _
                                 " DescSplit" & _
                                 " FROM Split" & _
                                 " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
                                 " AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
                                 " AND CodigoSplit = " & lblCodSplit.Text & ";"


                theSqlCommand.CommandText = theQueryString
                theSqlConnection.Open()

                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                While theSqlDataReader.Read()

                    If Not (IsDBNull(theSqlDataReader!DescSplit) = True) Then
                        txtDescSplit.Text = theSqlDataReader!DescSplit
                    End If

                End While

                theSqlDataReader.Close()

            Catch ex As Exception
                lblMsg.Text = ex.Message
            End Try

        End Using

    End Sub

    Private Function DataValid() As Boolean

        Dim dblSomaItemsDespesas As Double = 0

        DataValid = False

        If (ddlCompanhia.Text = "") Then
            lblMsg.Text = "Companhia deve ser selecionada."
            Exit Function
        End If

        If (Trim(txtDescSplit.Text) = "") Then
            lblMsg.Text = "Split deve ser preenchida."
            Exit Function
        End If

        DataValid = True

    End Function

    'Private Function VerifyData(ByVal strConn As String) As Boolean

    'Dim theQueryString As String = ""

    'VerifyData = False

    'Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

    '    Try
    '        Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()

    '        theQueryString = " SELECT DescSplit" & _
    '                         " FROM Split" & _
    '                         " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
    '                         " AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
    '                         " AND CodigoDoEstabelecimento = '" & ddlEstabelecimento.SelectedValue & "'" & _
    '                         " AND CodigoSplit = " & lblCodSplit.Text & ";"

    '        theSqlCommand.CommandText = theQueryString
    '        theSqlConnection.Open()

    '        Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

    '        If theSqlDataReader.Read() Then
    '            VerifyData = True
    '            lblMsg.Text = "Exclusão não permitida, existe relação do Split " & txtDescSplit.Text & " com ???."
    '        End If

    '        theSqlDataReader.Close()

    '    Catch ex As Exception
    '        VerifyData = True
    '        lblMsg.Text = ex.Message
    '    End Try

    'End Using

    'End Function


    Private Function AddRecord(ByVal strConn As String) As Boolean

        Dim theQueryString As String = ""

        AddRecord = False

        Dim nDiaCreated As String
        Dim nMesCreated As String
        Dim nAnoCreated As String
        Dim nLastUpdated As String

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Try
                theQueryString = "SELECT CONVERT(CHAR(10), GETDATE(), 103) AS DATADOSERVIDOR"

                Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()

                theSqlCommand.CommandText = theQueryString
                theSqlConnection.Open()

                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                Do While theSqlDataReader.Read()
                    Session("dDTAno") = Trim(theSqlDataReader(0))
                Loop

                theSqlDataReader.Close()

            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try

        End Using

        nDiaCreated = Mid(Session("dDTAno"), 1, 2)
        nMesCreated = Mid(Session("dDTAno"), 4, 2)
        nAnoCreated = Mid(Session("dDTAno"), 7, 4)
        nLastUpdated = nDiaCreated & "/" & nMesCreated & "/" & nAnoCreated

        If (IsDate(nLastUpdated)) Then
            nLastUpdated = "'" & FormatMediumDate(nLastUpdated) & "'"
        Else
            nLastUpdated = "Null"
        End If

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Try

                Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()

                theQueryString = " INSERT INTO Split" & _
                                 " (" & _
                                 " CodigoDaHolding," & _
                                 " CodigoDaCia," & _
                                 " DescSplit"

                theQueryString = theQueryString & _
                                 " CreateUser," & _
                                 " Created" & _
                                 " )"

                theQueryString = theQueryString & _
                                 " VALUES " & _
                                 " (" & _
                                 " '" & lblHolding.ToolTip & "'," & _
                                 " '" & ddlCompanhia.SelectedValue & "'," & _
                                 " '" & txtDescSplit.Text & "',"

                theQueryString = theQueryString & _
                         " '" & Session("sNM_USERNICKNAME").ToString & "'," & _
                         " " & nLastUpdated & _
                         " );"

                theSqlCommand.CommandText = theQueryString
                theSqlConnection.Open()

                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                theSqlDataReader.Close()

                AddRecord = True

            Catch ex As Exception
                lblMsg.Text = ex.Message
            End Try

        End Using

    End Function


    Private Function EditRecord(ByVal strConn As String) As Boolean

        Dim theQueryString As String = ""

        EditRecord = False

        Dim nDiaCreated As String
        Dim nMesCreated As String
        Dim nAnoCreated As String
        Dim nLastUpdated As String

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Try
                theQueryString = "SELECT CONVERT(CHAR(10), GETDATE(), 103) AS DATADOSERVIDOR"

                Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()

                theSqlCommand.CommandText = theQueryString
                theSqlConnection.Open()

                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                Do While theSqlDataReader.Read()
                    Session("dDTAno") = Trim(theSqlDataReader(0))
                Loop

                theSqlDataReader.Close()

            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try

        End Using

        nDiaCreated = Mid(Session("dDTAno"), 1, 2)
        nMesCreated = Mid(Session("dDTAno"), 4, 2)
        nAnoCreated = Mid(Session("dDTAno"), 7, 4)
        nLastUpdated = nDiaCreated & "/" & nMesCreated & "/" & nAnoCreated

        If (IsDate(nLastUpdated)) Then
            nLastUpdated = "'" & FormatMediumDate(nLastUpdated) & "'"
        Else
            nLastUpdated = "Null"
        End If

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Try
                Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()

                theQueryString = " UPDATE Split SET" & _
                                 " DescSplit = '" & txtDescSplit.Text & "'," 

                theQueryString = theQueryString & _
                                 " LastUser = '" & Session("sNM_USERNICKNAME").ToString & "'," & _
                                 " LastUpdated = " & nLastUpdated & ""

                theQueryString = theQueryString & _
                                 " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
                                 " AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
                                 " AND CodigoSplit = " & lblCodSplit.Text & ";"

                theSqlCommand.CommandText = theQueryString
                theSqlConnection.Open()

                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                theSqlDataReader.Close()

                EditRecord = True

            Catch ex As Exception
                lblMsg.Text = ex.Message
            End Try

        End Using

    End Function

    Private Function DeleteRecord(ByVal strConn As String) As Boolean

        Dim theQueryString As String = ""

        DeleteRecord = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Try

                Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()

                theQueryString = " DELETE FROM Split" & _
                                 " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
                                 " AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
                                 " AND CodigoSplit = " & lblCodSplit.Text & ";"

                theSqlCommand.CommandText = theQueryString
                theSqlConnection.Open()

                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                theSqlDataReader.Close()

                DeleteRecord = True

            Catch ex As Exception
                lblMsg.Text = ex.Message
            End Try

        End Using

    End Function

    Protected Sub btnGravar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGravar.Click

        Dim ConnectionTest As String
        ConnectionTest = "A"

        lblMsg.Text = ""

   
        If (Session("strOperacao") = "I") Then ' Incluir

            If Not (DataValid() = True) Then
                Exit Sub
            End If

            If Not (AddRecord(ConnectionTest) = True) Then
                Exit Sub
            End If

        ElseIf (Session("strOperacao") = "A") Then ' Alterar

            If Not (DataValid() = True) Then
                Exit Sub
            End If

            If Not (EditRecord(ConnectionTest) = True) Then
                Exit Sub
            End If

        ElseIf (Session("strOperacao") = "E") Then ' Excluir

            'If (VerifyData(ConnectionTest) = True) Then
            '    Exit Sub
            'End If

            If Not (DeleteRecord(ConnectionTest) = True) Then
                Exit Sub
            End If

        End If

        lblMsg.ForeColor = Drawing.Color.Green
        lblMsg.Text = "Operação realizada com sucesso. Clique no botão 'OK'"

        btnGravar.Enabled = False
        btnVoltar.Text = "OK"

    End Sub


    Protected Sub btnVoltar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnVoltar.Click

        Response.Redirect("Split.aspx")

    End Sub


    Public Function FormatMediumDate(ByVal DateValue)

        Dim strYYYY
        Dim strMM
        Dim strDD

        'Strip the Year
        strYYYY = CStr(DatePart("yyyy", DateValue))

        'Strip the Month	
        strMM = CStr(DatePart("m", DateValue))

        'See if month is a single digit if so display it as 01, 02, 03, etc
        If Len(strMM) = 1 Then strMM = "0" & strMM

        'Strip the Date	
        strDD = CStr(DatePart("d", DateValue))

        'See if the Day is a single digit day if so diaply it as 01, 02, 03, etc
        If Len(strDD) = 1 Then strDD = "0" & strDD

        'return the date formatted as DD/MM/YYY	
        FormatMediumDate = strYYYY & "/" & strMM & "/" & strDD

        'you could return the date many ways just retype the above string
        'FormatMediumDate = strDD & "-" & strMM & "-" & strYYYY
        'FormatMediumDate = strMM & "/" & strDD & "/" & strYYYY
        'FormatMediumDate = strYYYY & "-" & strMM & "-" & strMM

    End Function

End Class
